当前位置: 开发笔记 > 编程语言 > 正文

间隙|中西区_数据库篇复习篇

作者：Anruoxia52 | 来源：互联网 | 2023-09-18 09:11

篇首语：本文由编程笔记#小编为大家整理，主要介绍了数据库篇复习篇相关的知识，希望对你有一定的参考价值。数据库篇1.隔离级别

篇首语：本文由编程笔记#小编为大家整理，主要介绍了数据库篇复习篇相关的知识，希望对你有一定的参考价值。

数据库篇

1. 隔离级别

未提交读

读到其它事务未提交的数据&＃xff08;最新的版本&＃xff09;
错误现象&＃xff1a;有脏读、不可重复读、幻读现象

脏读现象

tx1	tx2
set session transaction isolation level read uncommitted;
start transaction;
select * from account; /两个账户都为 1000/
	start transaction;
	update account set balance &＃61; 2000 where accountNo&＃61;1;
select * from account; /1号账户2000, 2号账户1000/

tx2 未提交的情况下&＃xff0c;tx1 仍然读取到了它的更改

提交读&＃xff08;RC&＃xff09;

读到其它事务已提交的数据&＃xff08;最新已提交的版本&＃xff09;
错误现象&＃xff1a;有不可重复读、幻读现象
使用场景&＃xff1a;希望看到最新的有效值

不可重复度现象

tx1	tx2
set session transaction isolation level read committed;
start transaction;
select * from account; /两个账户都为 1000/
	update account set balance &＃61; 2000 where accountNo&＃61;1;
select * from account; /1号账户2000, 2号账户1000/

tx1 在同一事务内&＃xff0c;两次读取的结果不一致&＃xff0c;当然&＃xff0c;此时 tx2 的事务已提交

可重复读&＃xff08;RR&＃xff09;

在事务范围内&＃xff0c;多次读能够保证一致性&＃xff08;快照建立时最新已提交版本&＃xff09;
错误现象&＃xff1a;有幻读现象&＃xff0c;可以用加锁避免
使用场景&＃xff1a;事务内要求更强的一致性&＃xff0c;但看到的未必是最新的有效值

幻读现象

tx1	tx2
set session transaction isolation level repeatable read;
start transaction;
select * from account; /存在 1,2 两个账户/
	insert into account values(3, 1000);
select * from account; /发现还是只有 1,2 两个账户/
insert into account values(3, 5000); /* ERROR 1062 (23000): Duplicate entry ‘3’ for key ‘PRIMARY’ */

tx1 查询时并没有发现 3 号账户&＃xff0c;执行插入时却发现主键冲突异常&＃xff0c;就好像出现了幻觉一样

加锁避免幻读

tx1	tx2
set session transaction isolation level repeatable read;
start transaction;
select * from account; /存在 1,2 两个账户/
select * from account where accountNo&＃61;3 for update;
	insert into account values(3, 1000); /* 阻塞 */
insert into account values(3, 5000);

在 for update 这行语句执行时&＃xff0c;虽然此时 3 号账户尚不存在&＃xff0c;但 mysql 在 repeatable read 隔离级别下会用间隙锁&＃xff0c;锁住 2 号记录与正无穷大之间的间隙
此时 tx2 想插入 3 号记录就不行了&＃xff0c;被间隙锁挡住了

串行读

在事务范围内&＃xff0c;仅有读读可以并发&＃xff0c;读写或写写会阻塞其它事务&＃xff0c;用这种办法保证更强的一致性
错误现象&＃xff1a;无

串行读避免幻读

tx1	tx2
set session transaction isolation level serializable;
start transaction;
select * from account; /* 存在 1,2 两个账户 */
	insert into account values(3, 1000); /* 阻塞 */
insert into account values(3, 5000);

串行读隔离级别下&＃xff0c;普通的 select 也会加共享读锁&＃xff0c;其它事务的查询可以并发&＃xff0c;但增删改就只能阻塞了

2. 快照读与当前读

当前读

即读取最新提交的数据

select … for update
select … lock in share mode
insert、update、delete&＃xff0c;都会按最新提交的数据进行操作

当前读本质上是基于锁的并发读操作

快照读

读取某一个快照建立时&＃xff08;可以理解为某一时间点&＃xff09;的数据&＃xff0c;也称为一致性读。快照读主要体现在 select 时&＃xff0c;而不同隔离级别下&＃xff0c;select 的行为不同

在 Serializable 隔离级别下 - 普通 select 也变成当前读&＃xff0c;即加共享读锁
在 RC 隔离级别下 - 每次 select 都会建立新的快照
在 RR 隔离级别下
- 事务启动后&＃xff0c;首次 select 会建立快照
- 如果事务启动选择了 with consistent snapshot&＃xff0c;事务启动时就建立快照
- 基于旧数据的修改操作&＃xff0c;会重新建立快照

快照读本质上读取的是历史数据&＃xff08;原理是回滚段&＃xff09;&＃xff0c;属于无锁查询

RR 下&＃xff0c;快照建立时机 - 第一次 select 时

tx1	tx2
set session transaction isolation level repeatable read;
start transaction;
select * from account; /* 此时建立快照&＃xff0c;两个账户为 1000 */
	update account set balance &＃61; 2000 where accountNo&＃61;1;
select * from account; /* 两个账户仍为 1000 */

快照一旦建立&＃xff0c;以后的查询都基于此快照&＃xff0c;因此 tx1 中第二次 select 仍然得到 1 号账户余额为 1000

如果 tx2 的 update 先执行

tx1	tx2
set session transaction isolation level repeatable read;
start transaction;
	update account set balance &＃61; 2000 where accountNo&＃61;1;
select * from account; /* 此时建立快照&＃xff0c;1号余额已经为2000 */

RR 下&＃xff0c;快照建立时机 - 事务启动时

如果希望事务启动时就建立快照&＃xff0c;可以添加 with consistent snapshot 选项

tx1	tx2
set session transaction isolation level repeatable read;
start transaction with consistent snapshot; /* 此时建立快照&＃xff0c;两个账户为 1000 */
	update account set balance &＃61; 2000 where accountNo&＃61;1;
select * from account; /* 两个账户仍为 1000 */

RR 下&＃xff0c;快照建立时机 - 修改数据时

tx1	tx2
set session transaction isolation level repeatable read;
start transaction;
select * from account; /* 此时建立快照&＃xff0c;两个账户为 1000 */
	update account set balance&＃61;balance&＃43;1000 where accountNo&＃61;1;
update account set balance&＃61;balance&＃43;1000 where accountNo&＃61;1;
select * from account; /* 1号余额为3000 */

tx1 内的修改必须重新建立快照&＃xff0c;否则&＃xff0c;就会发生丢失更新的问题

3. InnoDB vs MyISAM

InnoDB

索引分为聚簇索引与二级索引
- 聚簇索引&＃xff1a;主键值作为索引数据&＃xff0c;叶子节点还包含了所有字段数据&＃xff0c;索引和数据是存储在一起的
- 二级索引&＃xff1a;除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据&＃xff0c;叶子节点还包含了主键值
支持事务
- 通过 undo log 支持事务回滚、当前读&＃xff08;多版本查询&＃xff09;
- 通过 redo log 实现持久性
- 通过两阶段提交实现一致性
- 通过当前读、锁实现隔离性
支持行锁、间隙锁
支持外键

MyISAM

索引只有一种
- 被索引字段值作为索引数据&＃xff0c;叶子节点还包含了该记录数据页地址&＃xff0c;数据和索引是分开存储的
不支持事务&＃xff0c;没有 undo log 和 redo log
仅支持表锁
不支持外键
会保存表的总行数

InnoDB 索引特点

聚簇索引&＃xff1a;主键值作为索引数据&＃xff0c;叶子节点还包含了所有字段数据&＃xff0c;索引和数据是存储在一起的

主键即 7369、7499、7521 等

二级索引&＃xff1a;除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据&＃xff0c;叶子节点还包含了主键值

上图中 800、950、1100 这些是工资字段的值&＃xff0c;根据它们建立了二级索引

上图中&＃xff0c;如果执行查询 select empno, ename, sal from emp where sal &＃61; 800&＃xff0c;这时候可以利用二级索引定位到 800 这个工资&＃xff0c;同时还能知道主键值 7369
但 select 字句中还出现了 ename 字段&＃xff0c;在二级索引中不存在&＃xff0c;因此需要根据主键值 7369 查询聚簇索引来获取 ename 的信息&＃xff0c;这个过程俗称回表

MyISAM 索引特点

被索引字段值作为索引数据&＃xff0c;叶子节点还包含了该记录数据页地址&＃xff0c;数据和索引是分开存储的

4. 索引

索引基础

常见索引

哈希索引
- 理想时间复杂度为 $O (1)$
- 适用场景&＃xff1a;适用于等值查询的场景&＃xff0c;内存数据的索引
- 典型实现&＃xff1a;Redis&＃xff0c;MySQL 的 memory 引擎
平衡二叉树索引
- 查询和更新的时间复杂度都是 $O(log_2(n))$
- 适用场景&＃xff1a;适用于等值查询以及范围查询&＃xff1b;适合内存数据的索引&＃xff0c;但不适合磁盘数据的索引&＃xff0c;可以认为树的高度决定了磁盘 I/O 的次数&＃xff0c;百万数据树高约为 20
BTree 索引
- BTree 其实就是 n 叉树&＃xff0c;分叉多意味着节点中的孩子&＃xff08;key&＃xff09;多&＃xff0c;树高自然就降低了
- 分叉数由页大小和行&＃xff08;包括 key 与 value&＃xff09;大小决定
  - 假设页大小为 16k&＃xff0c;每行 40 个字节&＃xff0c;那么分叉数就为 16k / 40 ≈ 410
  - 而分叉为 410&＃xff0c;则百万数据树高约为3&＃xff0c;仅 3 次 I/O 就能找到所需数据
- 局部性原理&＃xff1a;每次 I/O 按页为单位读取数据&＃xff0c;把多个 key 相邻的行放在同一页中&＃xff08;每页就是树上一个节点&＃xff09;&＃xff0c;能进一步减少 I/O
B&＃43; 树索引
- 在 BTree 的基础上做了改进&＃xff0c;索引上只存储 key&＃xff0c;这样能进一步增加分叉数&＃xff0c;假设 key 占 13 个字节&＃xff0c;那么一页数据分叉数可以到 1260&＃xff0c;树高可以进一步下降为 2

树高计算公式

$log_10(N) / log_10(M)$ 其中 N 为数据行数&＃xff0c;M 为分叉数

BTree vs B&＃43;Tree

无论 BTree 还是 B&＃43;Tree&＃xff0c;每个叶子节点到根节点距离都相同
BTree key 及 value 在每个节点上&＃xff0c;无论叶子还是非叶子节点

B&＃43;Tree 普通节点只存 key&＃xff0c;叶子节点才存储 key 和 value&＃xff0c;因此分叉数可以更多
- 不过也请注意&＃xff0c;普通节点上的 key 有的会与叶子节点的 key 重复
B&＃43;Tree 必须到达叶子节点才能找到 value
B&＃43;Tree 叶子节点用链表连接&＃xff0c;可以方便范围查询及全表遍历

注&＃xff1a;这两张图都是仅画了 key&＃xff0c;未画 value

B&＃43;Tree 新增 key

假设阶数&＃xff08;m&＃xff09;为5

若为空树&＃xff0c;那么直接创建一个节点&＃xff0c;插入 key 即可&＃xff0c;此时这个叶子结点也是根结点。例如&＃xff0c;插入 5
插入时&＃xff0c;若当前结点 key 的个数小于阶数&＃xff0c;则插入结束
依次插入 8、10、15&＃xff0c;按 key 大小升序
插入 16&＃xff0c;这时到达了阶数限制&＃xff0c;所以要进行分裂
叶子节点分裂规则&＃xff1a;将这个叶子结点分裂成左右两个叶子结点&＃xff0c;左叶子结点包含前 m/2 个&＃xff08;2个&＃xff09;记录&＃xff0c;右结点包含剩下的记录&＃xff0c;将中间的 key 进位到父结点中。注意&＃xff1a;中间的 key 仍会保留在叶子节点一份
插入 17
插入 18&＃xff0c;这时当前结点的 key 个数到达 5&＃xff0c;进行分裂
分裂成两个结点&＃xff0c;左结点 2 个记录&＃xff0c;右结点 3 个记录&＃xff0c;key 16 进位到父结点中
插入 19、20、21、22、6、9
插入 7&＃xff0c;当前结点的 key 个数到达 5&＃xff0c;需要分裂
分裂后 key 7 进入到父结点中&＃xff0c;这时父节点 key 个数也到达 5
非叶子节点分裂规则&＃xff1a;左子结点包含前 (m-1)/2 个 key&＃xff0c;将中间的 key 进位到父结点中&＃xff08;不保留&＃xff09;&＃xff0c;右子节点包含剩余的 key

B&＃43;Tree 查询 key

以查询 15 为例

第一次 I/O
第二次 I/O

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j5wRJyQm-1659863800788)(img/image-20210901175738876-16304902605912.png)]
第三次 I/O

B&＃43;Tree 删除叶子节点 key

初始状态
删完有富余。即删除后结点的key的个数 > m/2 – 1&＃xff0c;删除操作结束&＃xff0c;例如删除 22
删完没富余&＃xff0c;但兄弟节点有富余。即兄弟结点 key 有富余&＃xff08; > m/2 – 1 &＃xff09;&＃xff0c;向兄弟结点借一个记录&＃xff0c;同时替换父节点&＃xff0c;例如删除 15
兄弟节点也不富余&＃xff0c;合并兄弟叶子节点。即兄弟节点合并成一个新的叶子结点&＃xff0c;并删除父结点中的key&＃xff0c;将当前结点指向父结点&＃xff0c;例如删除 7
也需要删除非叶子节点中的 7&＃xff0c;并替换父节点保证区间仍有效
左右兄弟都不够借&＃xff0c;合并

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VLOf8HNd-1659863800793)(img/image-20210901180446827.png)]

B&＃43;Tree 删除非叶子节点 key

接着上面的操作

非叶子节点 key 的个数 > m/2 – 1&＃xff0c;则删除操作结束&＃xff0c;否则执行 2
若兄弟结点有富余&＃xff0c;父结点 key 下移&＃xff0c;兄弟结点 key 上移&＃xff0c;删除结束&＃xff0c;否则执行 3
若兄弟节点没富余&＃xff0c;当前结点和兄弟结点及父结点合并成一个新的结点。重复 1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rIJW9sM7-1659863800794)(img/image-20210901180511685.png)]

命中索引

准备数据

修改 MySQL 配置文件&＃xff0c;在 [mysqld] 下添加 secure_file_priv&＃61; 重启 MySQL 服务器&＃xff0c;让选项生效

执行 db.sql 内的脚本&＃xff0c;建表

执行 LOAD DATA INFILE &＃39;D:\\\\big_person.txt&＃39; INTO TABLE big_person; 注意实际路径根据情况修改

测试表 big_person&＃xff08;此表数据量较大&＃xff0c;如果与其它表数据一起提供不好管理&＃xff0c;故单独提供&＃xff09;&＃xff0c;数据行数 100 万条&＃xff0c;列个数 15 列。为了更快速导入数据&＃xff0c;这里采用了 load data infile 命令配合 *.txt 格式数据

索引用于排序

/* 测试单列索引并不能在多列排序时加速 */ create index first_idx on big_person(first_name); create index last_idx on big_person(last_name); explain select * from big_person order by last_name, first_name limit 10; /* 多列排序需要用组合索引 */ alter table big_person drop index first_idx; alter table big_person drop index last_idx; create index last_first_idx on big_person(last_name,first_name); /* 多列排序需要遵循最左前缀原则, 第1个查询可以利用索引&＃xff0c;第2,3查询不能利用索引 */ explain select * from big_person order by last_name, first_name limit 10; explain select * from big_person order by first_name, last_name limit 10; explain select * from big_person order by first_name limit 10; /* 多列排序升降序需要一致&＃xff0c;查询1可以利用索引&＃xff0c;查询2不能利用索引*/ explain select * from big_person order by last_name desc, first_name desc limit 10; explain select * from big_person order by last_name desc, first_name asc limit 10;

最左前缀原则

若建立组合索引 (a,b,c)&＃xff0c;则可以利用到索引的排序条件是&＃xff1a;

order by a
order by a, b
order by a, b, c

索引用于 where 筛选

参考 https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

/* 模糊查询需要遵循字符串最左前缀原则&＃xff0c;查询2可以利用索引&＃xff0c;查询1,3不能利用索引 */ explain SELECT * FROM big_person WHERE first_name LIKE &＃39;dav%&＃39; LIMIT 5; explain SELECT * FROM big_person WHERE last_name LIKE &＃39;dav%&＃39; LIMIT 5; explain SELECT * FROM big_person WHERE last_name LIKE &＃39;%dav&＃39; LIMIT 5; /* 组合索引需要遵循最左前缀原则&＃xff0c;查询1,2可以利用索引&＃xff0c;查询3,4不能利用索引 */ create index province_city_county_idx on big_person(province,city,county); explain SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND city&＃61;&＃39;宜兰县&＃39; AND county&＃61;&＃39;中西区&＃39;; explain SELECT * FROM big_person WHERE county&＃61;&＃39;中西区&＃39; AND city&＃61;&＃39;宜兰县&＃39; AND province &＃61; &＃39;上海&＃39;; explain SELECT * FROM big_person WHERE city&＃61;&＃39;宜兰县&＃39; AND county&＃61;&＃39;中西区&＃39;; explain SELECT * FROM big_person WHERE county&＃61;&＃39;中西区&＃39;; /* 函数及计算问题&＃xff0c;一旦在字段上应用了计算或函数&＃xff0c;都会造成索引失效。查询2可以利用索引&＃xff0c;查询1不能利用索引 */ create index birthday_idx on big_person(birthday); explain SELECT * FROM big_person WHERE ADDDATE(birthday,1)&＃61;&＃39;2005-02-10&＃39;; explain SELECT * FROM big_person WHERE birthday&＃61;ADDDATE(&＃39;2005-02-10&＃39;,-1); /* 隐式类型转换问题 * 查询1会发生隐式类型转换等价于在phone上应用了函数&＃xff0c;造成索引失效 * 查询2字段与值类型相同不会类型转换&＃xff0c;可以利用索引 */ create index phone_idx on big_person(phone); explain SELECT * FROM big_person WHERE phone &＃61; 13000013934; explain SELECT * FROM big_person WHERE phone &＃61; &＃39;13000013934&＃39;;

最左前缀原则&＃xff08;leftmost prefix&＃xff09;

若建立组合索引 (a,b,c)&＃xff0c;则可以利用到索引的查询条件是&＃xff1a;

where a &＃61; ?
where a &＃61; ? and b &＃61; ? &＃xff08;注意与条件的先后次序无关&＃xff0c;也可以是 where b &＃61; ? and a &＃61; ?&＃xff0c;只要出现即可&＃xff09;
where a &＃61; ? and b &＃61; ? and c &＃61; ? &＃xff08;注意事项同上&＃xff09;

不能利用的例子&＃xff1a;

where b &＃61; ?
where b &＃61; ? and c &＃61; ?
where c &＃61; ?

特殊情况&＃xff1a;

where a &＃61; ? and c &＃61; ?&＃xff08;a &＃61; ? 会利用索引&＃xff0c;但 c &＃61; ? 不能利用索引加速&＃xff0c;会触发索引条件下推&＃xff09;

索引条件下推

参考 https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

/* 查询 1,2,3,4 都能利用索引&＃xff0c;但 4 相当于部分利用了索引&＃xff0c;会触发索引条件下推 */ explain SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39;; explain SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND city&＃61;&＃39;嘉兴市&＃39;; explain SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND city&＃61;&＃39;嘉兴市&＃39; AND county&＃61;&＃39;中西区&＃39;; explain SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND county&＃61;&＃39;中西区&＃39;;

索引条件下推

MySQL 执行条件判断的时机有两处&＃xff1a;

服务层&＃xff08;上层&＃xff0c;不包括索引实现&＃xff09;
引擎层&＃xff08;下层&＃xff0c;包括了索引实现&＃xff0c;可以利用&＃xff09;
上面查询 4 中有 province 条件能够利用索引&＃xff0c;在引擎层执行&＃xff0c;但 county 条件仍然要交给服务层处理

在 5.6 之前&＃xff0c;服务层需要判断所有记录的 county 条件&＃xff0c;性能非常低
5.6 以后&＃xff0c;引擎层会先根据 province 条件过滤&＃xff0c;满足条件的记录才在服务层处理 county 条件

我们现在用的是 5.6 以上版本&＃xff0c;所以没有体会&＃xff0c;可以用下面的语句关闭索引下推优化&＃xff0c;再测试一下性能

SET optimizer_switch &＃61; &＃39;index_condition_pushdown&＃61;off&＃39;; SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND county&＃61;&＃39;中西区&＃39;;

二级索引覆盖

explain SELECT * FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND city&＃61;&＃39;宜兰县&＃39; AND county&＃61; &＃39;中西区&＃39;; explain SELECT id,province,city,county FROM big_person WHERE province &＃61; &＃39;上海&＃39; AND city&＃61;&＃39;宜兰县&＃39; AND county&＃61;&＃39;中西区&＃39;;

根据查询条件查询 1&＃xff0c;2 都会先走二级索引&＃xff0c;但是二级索引仅包含了 (province, city, county) 和 id 信息

查询 1 是 select *&＃xff0c;因此还有一些字段二级索引中没有&＃xff0c;需要回表&＃xff08;查询聚簇索引&＃xff09;来获取其它字段信息
查询 2 的 select 中明确指出了需要哪些字段&＃xff0c;这些字段在二级索引都有&＃xff0c;就避免了回表查询

其它注意事项

表连接需要在连接字段上建立索引
不要迷信网上说法&＃xff0c;具体情况具体分析

例如&＃xff1a;

create index first_idx on big_person(first_name); /* 不会利用索引&＃xff0c;因为优化器发现查询记录数太多&＃xff0c;还不如直接全表扫描 */ explain SELECT * FROM big_person WHERE first_name > &＃39;Jenni&＃39;; /* 会利用索引&＃xff0c;因为优化器发现查询记录数不太多 */ explain SELECT * FROM big_person WHERE first_name > &＃39;Willia&＃39;; /* 同一字段的不同值利用 or 连接&＃xff0c;会利用索引 */ explain select * from big_person where id &＃61; 1 or id &＃61; 190839; /* 不同字段利用 or 连接&＃xff0c;会利用索引(底层分别用了两个索引) */ explain select * from big_person where first_name &＃61; &＃39;David&＃39; or last_name &＃61; &＃39;Thomas&＃39;; /* in 会利用索引 */ explain select * from big_person where first_name in (&＃39;Mark&＃39;, &＃39;Kevin&＃39;,&＃39;David&＃39;); /* not in 不会利用索引的情况 */ explain select * from big_person where first_name not in (&＃39;Mark&＃39;, &＃39;Kevin&＃39;,&＃39;David&＃39;); /* not in 会利用索引的情况 */ explain select id from big_person where first_name not in (&＃39;Mark&＃39;, &＃39;Kevin&＃39;,&＃39;David&＃39;);

以上实验基于 5.7.27&＃xff0c;其它如 !&＃61;、is null、is not null 是否使用索引都会跟版本、实际数据相关&＃xff0c;以优化器结果为准

5. 查询语句执行流程

执行 SQL 语句 select * from user where id &＃61; 1 时发生了什么

连接器&＃xff1a;负责建立连接、检查权限、连接超时时间由 wait_timeout 控制&＃xff0c;默认 8 小时
查询缓存&＃xff1a;会将 SQL 和查询结果以键值对方式进行缓存&＃xff0c;修改操作会以表单位导致缓存失效
分析器&＃xff1a;词法、语法分析
优化器&＃xff1a;决定用哪个索引&＃xff0c;决定表的连接顺序等
执行器&＃xff1a;根据存储引擎类型&＃xff0c;调用存储引擎接口
存储引擎&＃xff1a;数据的读写接口&＃xff0c;索引、表都在此层实现

6. undo log 与 redo log

undo log

回滚数据&＃xff0c;以行为单位&＃xff0c;记录数据每次的变更&＃xff0c;一行记录有多个版本并存
多版本并发控制&＃xff0c;即快照读&＃xff08;也称为一致性读&＃xff09;&＃xff0c;让查询操作可以去访问历史版本

每个事务会按照开始时间&＃xff0c;分配一个单调递增的事务编号 trx id
每次事务的改动都会以行为单位记入回滚日志&＃xff0c;包括当时的事务编号&＃xff0c;改动的值等
查询操作&＃xff0c;事务编号大于自己的数据是不可见的&＃xff0c;事务编号小于等于自己的数据才是可见的
- 例如图中红色事务看不到 trx id&＃61;102 以及 trx id&＃61;101 的数据&＃xff0c;只有 trx id&＃61;99 的数据对它可见

redo log

redo log 的作用主要是实现 ACID 中的持久性&＃xff0c;保证提交的数据不丢失

它记录了事务提交的变更操作&＃xff0c;服务器意外宕机重启时&＃xff0c;利用 redo log 进行回放&＃xff0c;重新执行已提交的变更操作
事务提交时&＃xff0c;首先将变更写入 redo log&＃xff0c;事务就视为成功。至于数据页&＃xff08;表、索引&＃xff09;上的变更&＃xff0c;可以放在后面慢慢做
- 数据页上的变更宕机丢失也没事&＃xff0c;因为 redo log 里已经记录了
- 数据页在磁盘上位置随机&＃xff0c;写入速度慢&＃xff0c;redo log 的写入是顺序的速度快

它由两部分组成&＃xff0c;内存中的 redo log buffer&＃xff0c;磁盘上的 redo log file

redo log file 由一组文件组成&＃xff0c;当写满了会循环覆盖较旧的日志&＃xff0c;这意味着不能无限依赖 redo log&＃xff0c;更早的数据恢复需要 binlog
buffer 和 file 两部分组成意味着&＃xff0c;写入了文件才真正安全&＃xff0c;同步策略由参数 innodb_flush_log_at_trx_commit 控制
- 0 - 每隔 1s 将日志 write and flush 到磁盘
- 1 - 每次事务提交将日志 write and flush&＃xff08;默认值&＃xff09;
- 2 - 每次事务提交将日志 write&＃xff0c;每隔 1s flush 到磁盘&＃xff0c;意味着 write 意味着写入操作系统缓存&＃xff0c;如果 MySQL 挂了&＃xff0c;而操作系统没挂&＃xff0c;那么数据不会丢失

7. 锁

全局锁

用作全量备份时&＃xff0c;保证表与表之间的数据一致性

如果不加任何包含&＃xff0c;数据备份时就可能产生不一致的情况&＃xff0c;如下图所示

全局锁的语法&＃xff1a;

flush tables with read lock;

使用全局读锁锁定所有数据库的所有表。这时会阻塞其它所有 DML 以及 DDL 操作&＃xff0c;这样可以避免备份过程中的数据不一致。接下来可以执行备份&＃xff0c;最后用 unlock tables 来解锁

注意

但 flush tables 属于比较重的操作&＃xff0c;可以使用 --single-transaction 参数来完成不加锁的一致性备份&＃xff08;仅针对 InnoDB 引擎的表&＃xff09;

mysqldump --single-transaction -uroot -p test > 1.sql

表级锁 - 表锁

语法&＃xff1a;加锁 lock tables 表名 read/write&＃xff0c;解锁 unlock tables
缺点&＃xff1a;粒度较粗&＃xff0c;在 InnoDB 引擎很少使用

表级锁 - 元数据锁

即 metadata-lock&＃xff08;MDL&＃xff09;&＃xff0c;主要是为了避免 DML 与 DDL 冲突&＃xff0c;DML 的元数据锁之间不互斥
加元数据锁的几种情况
- lock tables read/write&＃xff0c;类型为 SHARED_READ_ONLY 和 SHARED_NO_READ_WRITE
- alter table&＃xff0c;类型为 EXCLUSIVE&＃xff0c;与其它 MDL 都互斥
- select&＃xff0c;select … lock in share mode&＃xff0c;类型为 SHARED_READ
- insert&＃xff0c;update&＃xff0c;delete&＃xff0c;select for update&＃xff0c;类型为 SHARED_WRITE
查看元数据锁&＃xff08;适用于 MySQL 8.0 以上版本&＃xff09;
- select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

表级锁 - IS&＃xff08;意向共享&＃xff09; 与 IX&＃xff08;意向排他&＃xff09;

主要是避免 DML 与表锁冲突&＃xff0c;DML 主要目的是加行锁&＃xff0c;为了让表锁不用检查每行数据是否加锁&＃xff0c;加意向锁&＃xff08;表级&＃xff09;来减少表锁的判断&＃xff0c;意向锁之间不会互斥
加意向表锁的几种情况
- select … lock in share mode 会加 IS 锁
- insert&＃xff0c;update&＃xff0c;delete&＃xff0c; select … for update 会加 IX 锁
查看意向表锁&＃xff08;适用于 MySQL 8.0 以上版本&＃xff09;
- select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

种类
- 行锁 – 在 RC 下&＃xff0c;锁住的是行&＃xff0c;防止其他事务对此行 update 或 delete
- 间隙锁 – 在 RR 下&＃xff0c;锁住的是间隙&＃xff0c;防止其他事务在这个间隙 insert 产生幻读
- 临键锁 – 在 RR 下&＃xff0c;锁住的是前面间隙&＃43;行&＃xff0c;特定条件下可优化为行锁
查看行级锁
- select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks where object_name&＃61;&＃39;表名&＃39;;

注意

它们锁定的其实都是索引上的行与间隙&＃xff0c;根据索引的有序性来确定间隙

测试数据

create table t (id int primary key, name varchar(10),age int, key (name)); insert into t values(1, &＃39;zhangsan&＃39;,18); insert into t values(2, &＃39;lisi&＃39;,20); insert into t values(3, &＃39;wangwu&＃39;,21); insert into t values(4, &＃39;zhangsan&＃39;, 17); insert into t values(8,&＃39;zhang&＃39;,18); insert into t values(12,&＃39;zhang&＃39;,20);

说明

1,2,3,4 之间其实并不可能有间隙
4 与 8 之间有间隙
8 与 12 之间有间隙
12 与正无穷大之间有间隙
其实我们的例子中还有负无穷大与 1 之间的间隙&＃xff0c;想避免负数可以通过建表时选择数据类型为 unsigned int

间隙锁例子

事务1&＃xff1a;

begin; select * from t where id &＃61; 9 for update; /* 锁住的是 8 与 12 之间的间隙 */

事务2&＃xff1a;

update t set age&＃61;100 where id &＃61; 8; /* 不会阻塞 */ update t set age&＃61;100 where id &＃61; 12; /* 不会阻塞 */ insert into t values(10,&＃39;aaa&＃39;,18); /* 会阻塞 */

临键锁和记录锁例子

事务1&＃xff1a;

begin; select * from t where id >&＃61; 8 for update;

临键锁锁定的是左开右闭的区间&＃xff0c;与上条查询条件相关的区间有 (4,8]&＃xff0c;(8,12]&＃xff0c;(12,&＃43;∞)
临键锁在某些条件下可以被优化为记录锁&＃xff0c;例如 (4,8] 被优化为只针对 8 的记录锁&＃xff0c;前面的区间不会锁住

事务2&＃xff1a;

insert into t values(7,&＃39;aaa&＃39;,18); /* 不会阻塞 */ update t set age&＃61;100 where id &＃61; 8; /* 会阻塞 */ insert into t values(




    
        
                        js
                        数据库
                        编程
                        session
                        io
                        select
                        insert
                        int
                        key
                    
    



    
        写下你的评论吧 !
        
            
                吐个槽吧,看都看了
            
            
                
                                        会员登录 | 用户注册
                                    
                
            
        

        
    

    
        推荐阅读
        
            
                                
                    
                        ip
                        Android 新闻App的本地服务器搭建教程
                    

                    
                                                
                        本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-14 17:15:19
                    

                

                
                                
                    
                        js
                        如何限制php数据库链接数和连接超时时间？
                    

                    
                                                
                        本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-14 14:06:10
                    

                

                                
                    
                    
                
                
                                
                    
                        js
                        [译]技术公司十年经验的职场生涯回顾
                    

                    
                                                
                            
                        
                                                
                        本文是一位在技术公司工作十年的职场人士对自己职业生涯的总结回顾。她的职业规划与众不同，令人深思又有趣。其中涉及到的内容有机器学习、创新创业以及引用了女性主义者在TED演讲中的部分讲义。文章表达了对职业生涯的愿望和希望，认为人类有能力不断改善自己。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-14 11:31:05
                    

                

                
                                
                    
                        get
                        SQL日志收缩及截断方法详解
                    

                    
                                                
                        本文详细介绍了SQL日志收缩的方法，包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时，还介绍了截断日志的原理和注意事项，包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法，可以有效减小逻辑日志的大小，提高数据库的性能。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-14 18:23:25
                    

                

                
                                
                    
                        js
                        如何使用Java获取服务器硬件信息和磁盘负载率
                    

                    
                                                
                            
                        
                                                
                        本文介绍了使用Java编程语言获取服务器硬件信息和磁盘负载率的方法。首先在远程服务器上搭建一个支持服务端语言的HTTP服务，并获取服务器的磁盘信息，并将结果输出。然后在本地使用JS编写一个AJAX脚本，远程请求服务端的程序，得到结果并展示给用户。其中还介绍了如何提取硬盘序列号的方法。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-14 13:56:20
                    

                

                
                                
                    
                        get
                        Spring特性实现接口多类的动态调用详解
                    

                    
                                                
                            
                        
                                                
                        本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-14 03:24:19
                    

                

                
                                
                    
                        sum
                        图解redis的持久化存储机制RDB和AOF的原理和优缺点
                    

                    
                                                
                            
                        
                                                
                        本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件，恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘，实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点，帮助读者更好地理解redis的持久化存储策略。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 20:24:11
                    

                

                
                                
                    
                        buffer
                        Java String与StringBuffer的区别及其应用场景
                    

                    
                                                
                            
                        
                                                
                        本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 19:21:06
                    

                

                
                                
                    
                        js
                        高校天文共享平台开发过程中的思考与规划
                    

                    
                                                
                        本文介绍了高校天文共享平台的开发过程中的思考和规划。该平台旨在为高校学生提供天象预报、科普知识、观测活动、图片分享等功能。文章分析了项目的技术栈选择、网站前端布局、业务流程、数据库结构等方面，并总结了项目存在的问题，如前后端未分离、代码混乱等。作者表示希望通过记录和规划，能够理清思路，进一步完善该平台。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 18:08:58
                    

                

                
                                
                    
                        sum
                        VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？
                    

                    
                                                
                        本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 15:15:30
                    

                

                
                                
                    
                        sum
                        SpringJdbcTemplate的使用详解
                    

                    
                                                
                        本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 14:27:11
                    

                

                
                                
                    
                        js
                        高质量SQL书写的30条建议
                    

                    
                                                
                            
                        
                                                
                        本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 13:24:33
                    

                

                
                                
                    
                        testing
                        问题2：重新命名或者移动数据文件、日志文件到新的位置
                    

                    
                                                
                        本文讨论了在数据库打开和关闭状态下，重新命名或移动数据文件和日志文件的情况。针对性能和维护原因，需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况，以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 13:02:24
                    

                

                
                                
                    
                        search
                        用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性
                    

                    
                                                
                        ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-13 09:49:28
                    

                

                
                                
                    
                        ip
                        如何在php中将mysql查询结果赋值给变量
                    

                    
                                                
                            
                        
                                                
                        本文介绍了在php中将mysql查询结果赋值给变量的方法，包括从mysql表中查询count(学号)并赋值给一个变量，以及如何将sql中查询单条结果赋值给php页面的一个变量。同时还讨论了php调用mysql查询结果到变量的方法，并提供了示例代码。 ...
                        [详细]
                    
                    

                    
                        蜡笔小新   2023-12-12 18:22:57

















    

    
        
            
            
                
                
            

            
                Anruoxia52            

            
                这个家伙很懒，什么也没留下！            


        
    

    
    

    
    

    
        Tags | 热门标签
        
            
                                
                    export
                
                                
                    stream
                
                                
                    select
                
                                
                    actionscrip
                
                                
                    flutter
                
                                
                    schema
                
                                
                    const
                
                                
                    hook
                
                                
                    dagger
                
                                
                    search
                
                                
                    javascript
                
                                
                    buffer
                
                                
                    match
                
                                
                    request
                
                                
                    testing
                
                                
                    ascii
                
                                
                    header
                
                                
                    netty
                
                                
                    ip
                
                                
                    dll
                
                                
                    client
                
                                
                    plugins
                
                                
                    jar
                
                                
                    js
                
                                
                    web3
                
                                
                    solr
                
                                
                    get
                
                                
                    sum
                
                                
                    split
                
                                
                    node.js
                
                                
            
        
    

    
    
        
            
            
        
        RankList | 热门文章
        
            
                                
                    1帆软报表数据可视化
                
                                
                    2全面复盘！深度剖析直播答题产品架构的难点与坑
                
                                
                    3win7任务栏右下角空白怎么办  Win7任务栏右下角图标空白的快速解决办法
                
                                
                    4amd同步多线程_AMD发布第三代锐龙3系列 首次多线程
                
                                
                    5win10系统开机之后时间用久了，点击关机之后，一直转圈圈，显示正在关机，过了1个小时也没关掉，求解决？
                
                                
                    6佳能Canon PIXMA MG2550 打印机驱动
                
                                
                    7从公司白领到自己多次创业经历，梦想总是要有的！
                
                                
                    8MQ分布式环境下保持数据一致性
                
                                
                    9七夕节通常吃什么美食呢？
                
                                
                    10Steam界面皮肤怎么更换？:idea皮肤设置
                
                                
                    1180所大学计算机考研真题开源库，免费下载！
                
                                
                    12饿了么数据采集app爬虫
                
                                
                    13关于自体脂肪填充要全麻吗？脂肪填充一年眼睛还肿？怎么回事啊？(转载)
                
                                
                    14WSUS 3.0 SP2 部署安装
                
                                
                    15能否把别人XP中的打印驱动文件拷到自己XP中？在线急等，解答立即给分。